﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CommonLibrary.DB;
using CommonLibrary.DataModel.WorkFlow;
using System.Data;

namespace ServiceApplication.Dao.WorkFlow
{
    public class WfDelegDao
    {
        public int AddEntity(WfDelegEntity wfDelegEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@deleg_id", wfDelegEntity.deleg_id));
            parameterList.Add(new SqlParameter("@deleg_wtr", wfDelegEntity.deleg_wtr));
            parameterList.Add(new SqlParameter("@deleg_str", wfDelegEntity.deleg_str));
            parameterList.Add(new SqlParameter("@deleg_stime", wfDelegEntity.deleg_stime));
            parameterList.Add(new SqlParameter("@deleg_etime", wfDelegEntity.deleg_etime));
            parameterList.Add(new SqlParameter("@deleg_status", wfDelegEntity.deleg_status));
            parameterList.Add(new SqlParameter("@deleg_descn", wfDelegEntity.deleg_descn));
            parameterList.Add(new SqlParameter("@deleg_accept", wfDelegEntity.deleg_accept));
            parameterList.Add(new SqlParameter("@deleg_rectime", wfDelegEntity.deleg_rectime));
            parameterList.Add(new SqlParameter("@deleg_starter", wfDelegEntity.deleg_starter));
            parameterList.Add(new SqlParameter("@deleg_closer", wfDelegEntity.deleg_closer));

            string sql = "insert into wf_deleg (deleg_wtr,deleg_str,deleg_stime,deleg_etime,deleg_status,deleg_descn,deleg_accept,deleg_rectime,deleg_starter,deleg_closer) values " +
                " (@deleg_wtr,@deleg_str,@deleg_stime,@deleg_etime,@deleg_status,@deleg_descn,@deleg_accept,@deleg_rectime,@deleg_starter,@deleg_closer); " +
                " select @@identity";
            string id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());
            return int.Parse(id);
        }

        public void UpadateEntity(WfDelegEntity wfDelegEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@deleg_id", wfDelegEntity.deleg_id));
            parameterList.Add(new SqlParameter("@deleg_wtr", wfDelegEntity.deleg_wtr));
            parameterList.Add(new SqlParameter("@deleg_str", wfDelegEntity.deleg_str));
            parameterList.Add(new SqlParameter("@deleg_stime", wfDelegEntity.deleg_stime));
            parameterList.Add(new SqlParameter("@deleg_etime", wfDelegEntity.deleg_etime));
            parameterList.Add(new SqlParameter("@deleg_status", wfDelegEntity.deleg_status));
            parameterList.Add(new SqlParameter("@deleg_descn", wfDelegEntity.deleg_descn));
            parameterList.Add(new SqlParameter("@deleg_accept", wfDelegEntity.deleg_accept));
            parameterList.Add(new SqlParameter("@deleg_rectime", wfDelegEntity.deleg_rectime));
            parameterList.Add(new SqlParameter("@deleg_starter", wfDelegEntity.deleg_starter));
            parameterList.Add(new SqlParameter("@deleg_closer", wfDelegEntity.deleg_closer));
            string sql = " update wf_deleg set deleg_wtr=@deleg_wtr,deleg_str=@deleg_str,deleg_stime=@deleg_stime,deleg_etime=@deleg_etime, " +
                             " deleg_status=@deleg_status,deleg_descn=@deleg_descn,deleg_accept=@deleg_accept,deleg_rectime=@deleg_rectime, " +
                             "deleg_starter=@deleg_starter,deleg_closer=@deleg_closer " +
                             " where deleg_id=@deleg_id ";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public WfDelegEntity QueryEntity(int deleg_id)
        {
            WfDelegEntity wfDelegEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@deleg_id", deleg_id));
            string sql = "select deleg_id,deleg_wtr,deleg_str,deleg_stime,deleg_etime,deleg_status,deleg_descn,deleg_accept,deleg_rectime,deleg_starter,deleg_closer from wf_deleg where deleg_id=@deleg_id";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (null == result || result.Length == 0) return null;

            wfDelegEntity = new WfDelegEntity();
            wfDelegEntity.deleg_id = int.Parse(result[0][0]);
            wfDelegEntity.deleg_wtr = int.Parse(result[0][1]);
            wfDelegEntity.deleg_str = int.Parse(result[0][2]);
            wfDelegEntity.deleg_stime = result[0][3];
            wfDelegEntity.deleg_etime = result[0][4];
            wfDelegEntity.deleg_status = result[0][5];
            wfDelegEntity.deleg_descn = result[0][6];
            wfDelegEntity.deleg_accept = result[0][7];
            wfDelegEntity.deleg_rectime = result[0][8];
            wfDelegEntity.deleg_starter = int.Parse(result[0][9]);
            wfDelegEntity.deleg_closer = int.Parse(result[0][10]);
            return wfDelegEntity;
        }

        /// <summary>
        ///  查询当前有效的委托 (一个用户当前只有一个有效委托)
        /// </summary>
        /// <param name="deleg_id"></param>
        /// <returns></returns>
        public WfDelegEntity QueryCurActiviy(int deleg_wtr)
        {
            WfDelegEntity wfDelegEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@deleg_wtr", deleg_wtr));
            string sql = " select deleg_id,deleg_wtr,deleg_str,deleg_stime,deleg_etime,deleg_status,deleg_descn,deleg_accept,deleg_rectime,deleg_starter,deleg_closer " +
                             " from wf_deleg where deleg_wtr=@deleg_wtr and deleg_status='1' ";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (null == result || result.Length == 0) return null;

            wfDelegEntity = new WfDelegEntity();
            wfDelegEntity.deleg_id = int.Parse(result[0][0]);
            wfDelegEntity.deleg_wtr = int.Parse(result[0][1]);
            wfDelegEntity.deleg_str = int.Parse(result[0][2]);
            wfDelegEntity.deleg_stime = result[0][3];
            wfDelegEntity.deleg_etime = result[0][4];
            wfDelegEntity.deleg_status = result[0][5];
            wfDelegEntity.deleg_descn = result[0][6];
            wfDelegEntity.deleg_accept = result[0][7];
            wfDelegEntity.deleg_rectime = result[0][8];
            wfDelegEntity.deleg_starter = int.Parse(result[0][9]);
            wfDelegEntity.deleg_closer = int.Parse(result[0][10]);
            return wfDelegEntity;
        }

        /// <summary>
        /// 查询所有当前有效委托
        /// </summary>
        /// <returns></returns>
        public DataTable QueryCurValid()
        {
            string sql = " select deleg_id 委托编码,deleg_wtr 委托人编码,user1.user_name 委托人,deleg_str 受托人编码,user2.user_name 受托人,deleg_starter 发起人编码,user3.user_name 发起人, " +
                             " deleg_stime 开始时间,deleg_etime 结束时间,deleg_status 状态,deleg_descn 备注,deleg_accept 接受标志,deleg_rectime 回复时间 " +
                             " from wf_deleg deleg,jc_user user1,jc_user user2,jc_user user3  " +
                             " where deleg.deleg_wtr=user1.user_id and deleg.deleg_str=user2.user_id and deleg.deleg_starter=user3.user_id  order by deleg_stime desc ";
            var result = DBExecuteUtil.querySqlTable(sql);
            return result;
        }

        /// <summary>
        /// 后2个参数可有可无
        /// </summary>
        /// <param name="deleg_str"></param>
        /// <param name="deleg_status"></param>
        /// <param name="deleg_accept"></param>
        /// <returns></returns>
        public DataTable QueryByStrId(int deleg_str)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@deleg_str", deleg_str));


            string sql = " select deleg_id 编码,deleg_wtr 委托人编码,u1.user_name 委托人,de1.dept_name 部门1,du1.duty_name 职位1," +
                             " deleg_str 受托人编码,u2.user_name 受托人,de2.dept_name 部门2,du2.duty_name 职位2,deleg_stime 开始时间,deleg_etime 结束时间,deleg_status 状态,  " +
                             " deleg_descn 描述,deleg_accept 接受标志,deleg_rectime 接受时间 from wf_deleg  deleg,jc_user u1,jc_user u2,jc_staff s1,jc_staff s2,jc_dept de1,jc_dept de2,jc_duty du1,jc_duty du2  " +
                             " where deleg.deleg_wtr=u1.user_id and deleg.deleg_str=u2.user_id " +
                             " and u1.staff_id=s1.staff_id and s1.dept_id=de1.dept_id and s1.duty_id=du1.duty_id " +
                             " and u2.staff_id=s2.staff_id and s2.dept_id=de2.dept_id and s2.duty_id=du2.duty_id  and deleg_str=@deleg_str and deleg_status='1' " +
                             " order by  deleg_stime desc";
            DataTable dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        public DataTable QueryByWtrId(int deleg_wtr)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@deleg_wtr", deleg_wtr));
            string sql = " select deleg_id 编码,deleg_wtr 委托人编码,u1.user_name 委托人,deleg_str 受托人编码,u2.user_name 受托人,deleg_stime 开始时间,deleg_etime 结束时间, " +
                             " deleg_status 状态,deleg_accept 接受标志,deleg_descn 描述,deleg_rectime 接受时间 from wf_deleg  deleg,jc_user u1,jc_user u2 " +
                             " where deleg.deleg_wtr=u1.user_id and deleg.deleg_str=u2.user_id and deleg_wtr=@deleg_wtr order by deleg_stime desc ";

            DataTable dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }
    }
}
